/**
 * 
 */
package database.dao;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.LinkedList;

import database.DBConnection;

import model.Attribute;
import model.subjects.Employee;
import model.subjects.Person;

/**
 * @author Triin
 *
 */
public class EmployeeDAO {
	static DBConnection db = new DBConnection();
	
	public LinkedList<Attribute> findEmployeeAttributes(int employeeId){
		String sql = "SELECT E.employee, P.person,P.first_name, P.last_name, SAT.type_name, SAT.data_type AS data_type, SA.value_number, SA.value_text FROM employee E INNER JOIN person P ON  E.person_fk=P.person "
				+"LEFT JOIN subject_attribute SA ON E.employee=SA.subject_fk INNER JOIN subject_attribute_type SAT ON SA.subject_attribute_type_fk = SAT.subject_attribute_type "
				+"WHERE SA.subject_type_fk=3 AND E.employee = "+employeeId+";";
		LinkedList<Attribute> attributes=null;
		
		try{
			ResultSet resultSet = db.executeQuery(sql);
			attributes = AttributeDAO.toAttributes(resultSet);
		}catch(SQLException e){
			System.out.println("A problem with SQL statement in finding attributes for employee");
			e.printStackTrace();
		}
		return attributes;
	}
	public static int makeEmployee(int person, int enterprise, int structUnit){
		CallableStatement cs;
		int employeeId=0;
		try {
			db.connect();
			
			cs = db.getConnection().prepareCall("{?=call f_make_employee(?, ?, ?)}");
			cs.registerOutParameter(1, Types.NUMERIC);
			cs.setInt(2, person);
			cs.setInt(3, enterprise);
			cs.setInt(4, structUnit);

			cs.execute();
			BigDecimal bd = (BigDecimal) cs.getObject(1);
			employeeId = bd.intValue();
			cs.close();
			db.closeConnection();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return employeeId;
	}
	public static LinkedList<Attribute> findAttributeNames(){
		return AttributeDAO.findAttributeNames(3);
	}
	public static LinkedList<String> findEmployeeRoleTypesFromDB(){
		String sql = "SELECT employee_role_type, type_name FROM Employee_role_type;";
		
		LinkedList<String> roleTypes = new LinkedList<String>();
		try{
			ResultSet resultSet = db.executeQuery(sql);
			while(resultSet.next()){
				//int type = resultSet.getInt("employee_role_type");
				String roleName = resultSet.getString("type_name");
				roleTypes.add(roleName);
			}
		}catch(SQLException e){
			System.out.println("A problem with SQL statement in finding employee role types");
			e.printStackTrace();
		}
		return roleTypes;
	}
	public static int getRoleId(String role){
		String sql = "SELECT employee_role_type, type_name FROM Employee_role_type WHERE UPPER(type_name) LIKE UPPER('"+role+"%');";
		int roleId = 0;
		try{
			ResultSet resultSet = db.executeQuery(sql);
			while(resultSet.next()){
				roleId = resultSet.getInt("employee_role_type");
			}
		}catch(SQLException e){
			System.out.println("A problem with SQL statement in finding persons");
			e.printStackTrace();
		}
		db.closeConnection();
		return roleId;
	}
	public static void giveRole(int roleId, int employeeId) throws SQLException{
		String sql = "INSERT INTO Employee_role(employee_fk, employee_role_type_fk, active) VALUES ("+employeeId+", "+roleId+", 'Y');";
		db.connect();
		
		Statement select = db.getConnection().createStatement();
		select.executeUpdate(sql);
		System.out.println("Roll sisestatud");
		
		db.closeConnection();
	}
	public static void removeRole(int roleId, int employeeId){
		String sql = "DELETE FROM Employee_role WHERE employee_fk="+employeeId+" AND employee_role_type_fk="+roleId+";";
		
		try{
			db.connect();
			Statement select = db.getConnection().createStatement();
			select.executeUpdate(sql);
			
			db.closeConnection();
		}catch(SQLException e){
			e.printStackTrace();
		}
		
		
	}
	public static Employee[] findEmployeesFromDB(int enterpriseId){
		String sql = "SELECT P.first_name, P.last_name, P.person, P.identity_code, e.employee, e.enterprise_fk, e.struct_unit_fk, "
				+"ER.employee_role_type_fk, ERT.type_name FROM Employee E INNER JOIN Person P ON P.person=E.person_fk "
				+"LEFT JOIN Employee_role ER ON ER.employee_fk=e.employee "
				+"LEFT JOIN Employee_role_type ERT ON ERT.employee_role_type=ER.employee_role_type_fk WHERE e.enterprise_fk="+enterpriseId+";";
		int i=0;
		LinkedList<Employee> employeeList = new LinkedList<Employee>();
		Employee employee = null;
		boolean found = false;
		try{
			ResultSet resultSet = db.executeQuery(sql);
			while(resultSet.next()){
				found=false;
				for(Employee e: employeeList){ //Check if this person is already in employees list
					if(e.getId() == resultSet.getInt("person")){
						found = true;
						employee = e;
					}
				}
				if(!found){ //If employee isn't on the list, let's create employee and put it in the list
					employee = new Employee();
					employee.setEmployeeId(resultSet.getInt("employee"));
					employee.setId(resultSet.getInt("person"));
					employee.setFirst_name(resultSet.getString("first_name"));
					employee.setLast_name(resultSet.getString("last_name"));
					employee.setIdentity_code(resultSet.getString("identity_code"));
					employeeList.add(employee);
					System.out.println("Uus t��taja " + resultSet.getString("first_name"));
					i++;
				}
				// Give employees their roles (their's if person id in that row is the same as they have)
				if(resultSet.getString("type_name") != null && resultSet.getInt("person")== employee.getId()){
					employee.addRole(resultSet.getString("type_name"));
					System.out.println("T��tajale lisati roll " + resultSet.getString("type_name"));
				}

			}
		}catch(SQLException e){
			System.out.println("A problem with SQL statement in finding employees");
			e.printStackTrace();
		}
		Employee[] persons = new Employee[i];
		return employeeList.toArray(persons);
	}

	public LinkedList<Attribute> findEmployeeAttributesFromDB(int employeeId){
		String sql = "SELECT E.employee, P.person,P.first_name, P.last_name, SAT.type_name, SA.value_number, SA.value_text FROM employee E INNER JOIN person P ON  E.person_fk=P.person "
			+"LEFT JOIN subject_attribute SA ON E.employee=SA.subject_fk INNER JOIN subject_attribute_type SAT ON SA.subject_attribute_type_fk = SAT.subject_attribute_type "
			+"WHERE SA.subject_type_fk=3 AND E.employee = "+employeeId+";";
		return null;
	}
}
